package com.mrobuy.jdvc.service.impl;

import com.gavel.utils.StringUtils;
import com.mrobuy.jdvc.dao.CommonDao;
import com.mrobuy.jdvc.entity.VCCategory;
import com.mrobuy.jdvc.entity.VCPurchaseSale;
import com.mrobuy.jdvc.service.VCPurchaseSaleService;
import com.mrobuy.util.Pagination;
import org.apache.poi.ss.util.WorkbookUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

@Service("vcPurchaseSaleService")
public class VCPurchaseSaleServiceImpl implements VCPurchaseSaleService {

    private static Logger LOG = LoggerFactory.getLogger(VCPurchaseSaleService.class);

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private CommonDao commonDao;

    @Override
    public VCPurchaseSale load(String cateId, String cateName) {
        return null;
    }

    @Override
    public Pagination query(int start, int length, VCPurchaseSale condition) {

        Pagination pagination = new Pagination();

        List<Object> params = new ArrayList<>();

        StringBuilder whereCond = new StringBuilder(" where 1 =1 ");

        if ( condition.getCate3()!=null && condition.getCate3().trim().length() > 0 ) {
            whereCond.append(" and c.name = ? ");
            params.add(condition.getCate3().trim() );
        }

        if ( condition.getPurchasercode()!=null && condition.getPurchasercode().length() > 0 ) {
            whereCond.append(" and p.PURCHASERCODE = ? ");
            params.add(condition.getPurchasercode().trim() );
        }

        if ( condition.getSalercode()!=null && condition.getSalercode().length() > 0 ) {
            whereCond.append(" and p.SALERCODE = ? ");
            params.add(condition.getSalercode().trim() );
        }

        if ( StringUtils.isNotBlank(condition.getCid()) ) {
            whereCond.append(" and c.ID = ? ");
            params.add(condition.getCid().trim() );
        }

        try {

            int count = jdbcTemplate.queryForObject("select count(*) from VC_CATEGORY c  left join VC_PURCHASE_SALE p on c.ID = p.CID " + whereCond.toString(), params.toArray(new Object[params.size()]), Integer.class);

            params.add(start);
            params.add(length);
            List<VCPurchaseSale> list = jdbcTemplate.query("select p.* from VC_CATEGORY c  left join VC_PURCHASE_SALE p on c.ID = p.CID " + whereCond.toString() + " limit ?, ?", params.toArray(new Object[params.size()]), new BeanPropertyRowMapper(VCPurchaseSale.class));


            pagination.setRecordsTotal(count);
            pagination.setRecordsFiltered(count);
            pagination.setData(list);
        } catch (Exception e) {
            LOG.error(e.getMessage());
        }

        return pagination;

    }

    @Override
    public void importPurchaseSale(InputStream inputStream) throws Exception {

        ConcurrentHashMap<String, VCCategory> nameCategoryMap = new ConcurrentHashMap<>();
        try {
            List<VCCategory> categories = jdbcTemplate.query("select * from VC_CATEGORY where LEVEL = ?", new BeanPropertyRowMapper(VCCategory.class), 3);
            if ( categories!=null && categories.size() > 0 ) {
                for (VCCategory category : categories) {
                    nameCategoryMap.put(category.getName(), category);
                }
            }
        } catch (Exception e) {
            LOG.error(e.getMessage(), e.getCause());
        }

        XSSFWorkbook wb = new XSSFWorkbook(inputStream);
        try {
            int sheetIndex = wb.getActiveSheetIndex();
            XSSFSheet sheet = wb.getSheetAt(sheetIndex);

            int maxRow = sheet.getLastRowNum();
            for (int row = 1; row <= maxRow; row++) {

                XSSFRow sheetRow = sheet.getRow(row);
                if ( sheetRow==null ) {
                    continue;
                }

                XSSFCell cell0 = sheetRow.getCell(0);
                if ( cell0==null || StringUtils.isBlank(cell0.getStringCellValue()) ) {
                    continue;
                }
                XSSFCell cell1 = sheetRow.getCell(1);
                if ( cell1==null || StringUtils.isBlank(cell1.getStringCellValue()) ) {
                    continue;
                }
                XSSFCell cell2 = sheetRow.getCell(2);
                if ( cell2==null || StringUtils.isBlank(cell2.getStringCellValue()) ) {
                    continue;
                }
                XSSFCell cell3 = sheetRow.getCell(3);
                if ( cell3==null || StringUtils.isBlank(cell3.getStringCellValue()) ) {
                    continue;
                }

                VCPurchaseSale purchaseSale = new VCPurchaseSale();
                purchaseSale.setId(Math.abs(cell0.getStringCellValue().hashCode()) + "-" + Math.abs(cell1.getStringCellValue().hashCode()));
                purchaseSale.setCate2(cell0.getStringCellValue());
                purchaseSale.setCate3(cell1.getStringCellValue());
                purchaseSale.setPurchasercode(cell2.getStringCellValue());
                purchaseSale.setSalercode(cell3.getStringCellValue());


                if ( purchaseSale.getCate3().contains("存储设备") ) {
                    LOG.info("ss");
                }

                if ( nameCategoryMap.containsKey(purchaseSale.getCate3()) ) {
                    purchaseSale.setCid(String.valueOf(nameCategoryMap.get(purchaseSale.getCate3()).getId()));
                }

                VCPurchaseSale exist = null;

                try {
                    exist = jdbcTemplate.queryForObject("select * from VC_PURCHASE_SALE where ID = ?", new BeanPropertyRowMapper<>(VCPurchaseSale.class), purchaseSale.getId());
                } catch (EmptyResultDataAccessException e) {

                } catch (Exception e) {
                    LOG.error(e.getMessage(), e.getCause());
                }

                if ( exist == null ) {
                    commonDao.insert(purchaseSale);
                } else {
                    commonDao.update(purchaseSale);
                }
            }
        } finally {
            wb.close();
        }


    }
}
